package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Hashtable;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.ActivityStatus;
import ro.gateway.aida.servlet.Constants;



/**
 * <p>Title: Romanian AIDA</p>
 * <p>Description: :D application</p>
 * <p>Copyright: Copyright (comparator) 2003</p>
 * <p>Company: Romania Development Gateway </p>
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @version 1.0-* @version $Id: ActivityStatusDB.java,v 1.1 2004/10/24 23:37:11 mihaipostelnicu Exp $
 */
public class ActivityStatusDB extends DBPersistenceManager
{
      private ActivityStatusDB( PersistenceToken token )
      {
            super( token );
      }

      public static ActivityStatusDB getManager( PersistenceToken token )
      {
            return new ActivityStatusDB( token );
      }


      private static Hashtable cache = new Hashtable();
      private static String HARMONIZED_STATUSES = "HARM_STATUSES";

      public ActivityStatus[] getHarmonized()
            throws SQLException
      {
            Connection con = getConnection();

            ActivityStatus[] result = null;
            ArrayList items = new ArrayList();


            try
            {
                  PreparedStatement ps = null;
                  ps = con.prepareStatement( GET_HARM );
                  ResultSet rs = ps.executeQuery();

                  ActivityStatus last_status = null;
                  while ( rs.next() )
                  {
                        long id = rs.getLong( 1 );
                        if ( ( last_status != null ) && ( last_status.getId() != id ) )
                              last_status = null;

                        if ( last_status != null )
                        {
                              String lang = rs.getString( 3 );
                              last_status.setStatus( lang, rs.getString( 4 ) );
                              last_status.setDescription( lang, rs.getString( 5 ) );
                        }
                        else
                        {
                              last_status = new ActivityStatus();
                              last_status.setSt_code( rs.getLong( 2 ) );
                              last_status.setId( id );

                              String lang = rs.getString( 3 );
                              last_status.setStatus( lang, rs.getString( 4 ) );
                              last_status.setDescription( lang, rs.getString( 5 ) );

                              items.add( last_status );
                        }
                  }

                  rs.close();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

            if ( items.size() > 0 )
            {
                  result = new ActivityStatus[ items.size() ];
                  items.toArray( result );
            }
            return result;
      }

      /**
       * Daca nu este precizat lang, adica lang==null
       * returneaza statusurile armonizate in toate
       * limbile
       *
       * @param lang
       * @return
       * @throws SQLException
       */
      public ActivityStatus[] getHarmonizedByLang( String lang )
            throws SQLException
      {

            if ( lang == null ) return null;

            Connection con = getConnection();

            ActivityStatus[] result = null;
            ArrayList items = new ArrayList();

            try
            {
                  PreparedStatement ps = null;
                  ps = con.prepareStatement( GET_HARM_BY_LANG );
                  ps.setString( 1, lang );
                  ResultSet rs = ps.executeQuery();

                  while ( rs.next() )
                  {
                        ActivityStatus item = new ActivityStatus();

                        item.setId( rs.getLong( 1 ) );
                        item.setSt_code( rs.getLong( 2 ) );
                        String status = rs.getString( 3 );
                        String desc = rs.getString( 4 );
                        item.setStatus( lang, status );
                        item.setDescription( lang, desc );

                        items.add( item );
                  }

                  rs.close();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

            if ( items.size() > 0 )
            {
                  result = new ActivityStatus[ items.size() ];
                  items.toArray( result );
            }
            return result;
      }

      public void insert( ActivityStatus status )
            throws SQLException
      {

            String[] langs = status.getAvailableLangs();
            if ( langs == null )
            {
                  throw new SQLException( "Invalid status : no lang :D " );
            }

            Connection con = getConnection();
            if ( status.getId() == -1 )
            {
                  status.setId( db_token.getIdgenerators().nextVal( Constants.IDGEN_ACT_STATUS ) );
            }

            try
            {
                  PreparedStatement ps = con.prepareStatement( INSERT );

                  for ( int i = 0; i < langs.length; i++ )
                  {
                        ps.clearParameters();
                        ps.setLong( 1, status.getId() );
                        ps.setLong( 2, status.getSt_code() );
                        ps.setString( 3, langs[ i ] );
                        String s_status = status.getStatus( langs[ i ] );
                        if ( s_status == null )
                        {
                              ps.clearParameters();
                              continue;
                        }
                        ps.setString( 4, s_status );
                        String description = status.getDescription( langs[ i ] );
                        if ( description == null )
                        {
                              ps.setNull( 5, Types.VARCHAR );
                        }
                        else
                        {
                              ps.setString( 5, description );
                        }

                        ps.executeUpdate();
                  }
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }
      }

      public long getLastId()
            throws SQLException
      {
            long result = 0;
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( GET_MAX_ID );
                  ResultSet rs = ps.executeQuery();
                  if ( rs.next() )
                  {
                        result = rs.getLong( 1 );
                  }
                  rs.close();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

            return result;
      }

      public void update(
            ActivityStatus status )
            throws SQLException
      {

            String langs[] = status.getAvailableLangs();
            if ( ( status.getId() == -1 ) || ( langs == null ) || ( langs.length < 1 ) )
            {
                  return;
            }

            Connection con = getConnection();
            delete( con, status.getId() );

            try
            {
                  PreparedStatement ps = con.prepareStatement( INSERT );
                  for ( int i = 0; i < langs.length; i++ )
                  {
                        ps.setLong( 1, status.getId() );
                        ps.setLong( 2, status.getSt_code() );
                        ps.setString( 3, langs[ i ] );
                        String s_status = status.getStatus( langs[ i ] );
                        if ( s_status == null )
                        {
                              ps.clearParameters();
                              continue;
                        }
                        ps.setString( 4, s_status );
                        String description = status.getDescription( langs[ i ] );
                        if ( description == null )
                        {
                              ps.setNull( 5, Types.VARCHAR );
                        }
                        else
                        {
                              ps.setString( 5, description );
                        }
                        ps.executeUpdate();
                  }
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

      }

      private void delete(
            Connection con,
            long id )
            throws SQLException
      {

            try
            {
                  PreparedStatement ps = con.prepareStatement( DELETE );
                  ps.setLong( 1, id );
                  ps.executeUpdate();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
      }

	public ActivityStatus getById( long id )
		 throws SQLException
	{

		 Connection con = getConnection();
		 ActivityStatus result = null;

		 try
		 {
			  PreparedStatement ps = con.prepareStatement( GET_BY_ID );
			  ps.setLong( 1, id );
			  ResultSet rs = ps.executeQuery();
			  while ( rs.next() )
			  {
				   if ( result == null )
				   {
					    result = new ActivityStatus();
					    result.setId( id );
					    result.setSt_code( rs.getLong( 1 ) );
				   }
				   String lang = rs.getString( 2 );
				   result.setStatus( lang, rs.getString( 3 ) );
				   result.setDescription( lang, rs.getString( 4 ) );
			  }
			  rs.close();
			  ps.close();
		 }
		 catch ( SQLException sqlEx )
		 {
			  throw sqlEx;
		 }
		 finally
		 {
			  con.close();
		 }

		 return result;
	}

	public ActivityStatus getByStCode( int st_code )
		 throws SQLException
	{

		 Connection con = getConnection();
		 ActivityStatus result = null;

		 try
		 {
			  PreparedStatement ps = con.prepareStatement( GET_BY_ST_CODE );
			  ps.setInt( 1, st_code );
			  ResultSet rs = ps.executeQuery();
			  while ( rs.next() )
			  {
				   if ( result == null )
				   {
					    result = new ActivityStatus();
					    result.setSt_code( st_code );
					    result.setId( rs.getLong( 1 ) );
				   }
				   String lang = rs.getString( 2 );
				   result.setStatus( lang, rs.getString( 3 ) );
				   result.setDescription( lang, rs.getString( 4 ) );
			  }
			  rs.close();
			  ps.close();
		 }
		 catch ( SQLException sqlEx )
		 {
			  throw sqlEx;
		 }
		 finally
		 {
			  con.close();
		 }

		 return result;
	}

      private static final String GET_MAX_ID =
            "SELECT max(id) FROM act_status";

      private static final String GET_HARM_BY_LANG =
            "SELECT id, st_code, status, description " +
            "FROM act_status " +
            "WHERE st_code IS NOT NULL " +
            "AND lang=? " +
            "ORDER BY id ASC, st_code ASC";

      private static final String GET_HARM =
            "SELECT id, st_code, lang, status, description " +
            "FROM act_status " +
            "WHERE st_code IS NOT NULL " +
            "ORDER BY id ASC, st_code ASC";

      private static final String INSERT =
            "INSERT INTO act_status (id, st_code, lang, status, description) " +
            "VALUES (?,?,?,?,?)";

      private static final String DELETE =
            "DELETE FROM act_status WHERE id = ?";

	private static final String GET_BY_ID =
		 "SELECT st_code, lang, status, description " +
		 "FROM act_status " +
		 "WHERE id = ?";

	private static final String GET_BY_ST_CODE =
		 "SELECT id, lang, status, description " +
		 "FROM act_status " +
		 "WHERE st_code = ?";
}
